1. Technical Field
This invention generally relates to computer systems, and more specifically relates to apparatus and methods for optimizing the run-time performance of a database query.
2. Background Art
Since the dawn of the computer age, computers have evolved and become more and more powerful. In our present day, computers have become indispensable in many fields of human endeavor including engineering design, machine and process control, information storage and retrieval, and office computing. One of the primary uses of computers is for information storage and retrieval.
Database systems have been developed that allow a computer to store a large amount of information in a way that allows a user to search for and retrieve specific information in the database. For example, an insurance company may have a database that includes all of its policy holders and their current account information, including payment history, premium amount, policy number, policy type, exclusions to coverage, etc. A database system allows the insurance company to retrieve the account information for a single policy holder among the thousands and perhaps millions of policy holders in its database.
Retrieval of information from a database is typically done using queries. A query usually specifies conditions that apply to one or more columns of the database, and may specify relatively complex logical operations on multiple columns. The database is searched for records that satisfy the query, and those records are returned as the query result.
Queries that are frequently run may be optimized to enhance system performance. One known way to optimize a query is to build an access plan the first time a query is run, then reuse the access plan for subsequent executions of the same query. Note, however, that the performance of the query may be fine at the point in time when the access plan was generated, but due to changes in the database, software, etc., the same access plan may experience a marked slowdown in query performance. In addition, because the initial access plan is based on an estimate of performance instead of on actual execution time, the initial access plan may not be a very efficient implementation of the query. In the prior art, there is no way for the database optimizer to detect when an access plan performs poorly. Without a way to specify desired performance for a query and autonomically generate a new access plan when the current access plan no longer satisfies the desired performance, the database industry will continue to suffer from undue performance penalties by continually reusing access plans that provide poor performance in executing a query.